package com.lzyyj.gmis.base.model;

import java.io.IOException;
import java.lang.reflect.Executable;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class DataTable {

	private static Log log = LogFactory.getLog(DataTable.class);

	private int index;

	private Sheet sheet;

	public Sheet getSheet() {
		return sheet;
	}

	public void setSheet(Sheet sheet) {
		this.sheet = sheet;
	}

	/**
	 * 有效数据区起始行号
	 */
	private int firstRowNum = -1;

	public int getFirstRowNum() {
		return firstRowNum;
	}

	/**
	 * 有效数据区起始列号
	 */
	private int firstColNum = -1;

	public int getFirstColNum() {
		return firstColNum;
	}

	/**
	 * 有效数据区结束行号
	 */
	private int lastRowNum = -1;

	public int getLastRowNum() {
		return lastRowNum;
	}

	/**
	 * 有效数据区结束列号
	 */
	private int lastColNum = -1;

	public int getLastColNum() {
		return lastColNum;
	}

	/**
	 * 表格起始行号
	 */
	private int tableFirstRowNum = -1;

	public int getTableFirstRowNum() {
		return tableFirstRowNum;
	}

	/**
	 * 表格起始列号
	 */
	private int tableFirstColNum = -1;

	public int getTableFirstColNum() {
		return tableFirstColNum;
	}

	/**
	 * 表格结束行号
	 */
	private int tableLastRowNum = -1;

	public int getTableLastRowNum() {
		return tableLastRowNum;
	}

	/**
	 * 表格结束列号
	 */
	private int tableLastColNum = -1;

	public int getTableLastColNum() {
		return tableLastColNum;
	}

	/**
	 * 表头起始行号
	 */
	private int headFirstRowNum = -1;

	public int getHeadFirstRowNum() {
		return headFirstRowNum;
	}

	public void setHeadFirstRowNum(int headFirstRowNum) {
		this.headFirstRowNum = headFirstRowNum;
	}

	/**
	 * 表头结束行号
	 */
	private int headLastRowNum = -1;

	public int getHeadLastRowNum() {
		return headLastRowNum;
	}

	public void setHeadLastRowNum(int headLastRowNum) {
		this.headLastRowNum = headLastRowNum;
	}

	/**
	 * 栏目编号行起始行
	 */
	private int colCodeFirstRowNum = -1;

	/**
	 * 栏目编号行结束行
	 */
	private int colCodeLastRowNum = -1;

	private int firstRecordColNum = -1;

	public int getFirstRecordColNum() {
		return firstRecordColNum;
	}

	public void setFirstRecordColNum(int firstRecordColNum) {
		this.firstRecordColNum = firstRecordColNum;
	}

	private int lastRecordColNum = -1;

	public int getLastRecordColNum() {
		return lastRecordColNum;
	}

	public void setLastRecordColNum(int lastRecordColNum) {
		this.lastRecordColNum = lastRecordColNum;
	}

	private int lastRecordRowNum = -1;

	public int getLastRecordRowNum() {
		return lastRecordRowNum;
	}

	public void setLastRecordRowNum(int lastRecordRowNum) {
		this.lastRecordRowNum = lastRecordRowNum;
	}

	/**
	 * 表尾起始行号
	 */
	private int footerFirstRowNum = -1;

	public int getFooterFirstRowNum() {
		return footerFirstRowNum;
	}

	public void setFooterFirstRowNum(int footerFirstRowNum) {
		this.footerFirstRowNum = footerFirstRowNum;
	}

	/**
	 * 表尾结束行号
	 */
	private int footerLastRowNum = -1;

	public int getFooterLastRowNum() {
		return footerLastRowNum;
	}

	public void setFooterLastRowNum(int footerLastRowNum) {
		this.footerLastRowNum = footerLastRowNum;
	}

	/**
	 * 是否有标题
	 */
	private boolean hasTitle = false;

	public boolean isHasTitle() {
		return hasTitle;
	}

	/**
	 * 表格标题内容
	 */
	private String title;

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		hasTitle = title != null && title.length() > 0;
		this.title = title;
	}

	private int titleRowNum = -1;

	public int getTitleRowNum() {
		return titleRowNum;
	}

	public void setTitleRowNum(int titleRowNum) {
		this.titleRowNum = titleRowNum;
	}

	private int titleColNum = -1;

	public int getTitleColNum() {
		return titleColNum;
	}

	public void setTitleColNum(int titleColNum) {
		this.titleColNum = titleColNum;
	}

	/**
	 * 表格标题数据位置 左上角第一单元格为 A1
	 */
	public String getTitleLocation() {
		String titleLocation = null;
		if (titleRowNum >= 0 && titleColNum >= 0) {
			titleLocation = getColName(titleColNum) + (titleRowNum);
		}
		return titleLocation;
	}

	/**
	 * 序号列
	 */
	private int indexNumberColNum = -1;

	private FieldTree fieldTree = null;

	public FieldTree getFieldTree() {
		return fieldTree;
	}

	public void setFieldTree(FieldTree fieldTree) {
		this.fieldTree = fieldTree;
	}

	public DataTable() {
	}

	/**
	 * 
	 * @param sheet
	 * @param index
	 * @param firstRowNum
	 * @param firstColNum
	 * @param lastRowNum
	 * @param lastColNum
	 * @param list
	 */
	public DataTable(Sheet sheet, int index, int firstRowNum, int firstColNum, int lastRowNum, int lastColNum, List<DataTable> list) {
		// 复制公共属性
		this.sheet = sheet;
		this.index = index;
		
		// 初始有效数据区范围
		// 排除已解析区域
		if(list!=null&&list.size()>0) {
			boolean reDo;
			do {
				reDo = false;
				// 初始列数
				int colCount = lastColNum - firstColNum +1;
				for (DataTable excelDataTable : list) {
					// 当前扫描最小行开始三行内起始的表格，如果右边剩余列数过少，扫描起始行下推至该表结束
					if (excelDataTable.getTableFirstRowNum() >= firstRowNum && excelDataTable.getTableFirstRowNum() - firstRowNum <= 5) {
						colCount = colCount - (excelDataTable.getTableLastColNum() - excelDataTable.getTableFirstColNum() + 1);
						if (colCount <= 3) {
							// 右边剩余列数不足，起始行下推到该区域结束后，
							firstRowNum = excelDataTable.getTableLastRowNum() + 1;
							reDo = true;
							break;
						}
					}
					// 或者在当前扫描最小起始行还未结束的表格，如果右边剩余列数过少，扫描起始行下推至该表结束
					if (firstRowNum >= excelDataTable.getTableFirstRowNum() && firstRowNum <= excelDataTable.getTableLastRowNum()) {
						colCount = colCount - (excelDataTable.getTableLastColNum() - excelDataTable.getTableFirstColNum() + 1);
						if (colCount <= 3) {
							// 右边剩余列数不足，起始行下推到该区域结束后，
							firstRowNum = excelDataTable.getTableLastRowNum() + 1;
							reDo = true;
							break;
						}
					}

				}
			} while (reDo);
		}
		if(firstRowNum<=lastRowNum) {
			this.firstRowNum = firstRowNum;
			this.firstColNum = firstColNum;
			this.lastRowNum = lastRowNum;
			this.lastColNum = lastColNum;
			parseTableRange(list);
			hasTitle = parseTitle();
			parseRecordRange(list);
		}else {
			this.firstRowNum = -1;
			this.firstColNum = -1;
			this.lastRowNum = -1;
			this.lastColNum = -1;
		}
	}

	/**
	 * 解析有效数据区范围, 从上到下，从左到右，从有内容或有表格线开始，到最后有内容结束
	 */
	private static Map<String, Integer> parseDataRange(Sheet sheet) {
		Map<String, Integer> result = null;
		
		int firstRowNum = -1;
		int firstColNum = -1;
		int lastRowNum = -1;
		int lastColNum = -1;
		
		// 分析有效区域，从外围往中间搜索有信息的单元格，以确定有效数据范围

		// 行首范围, 正向搜索
		for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (row != null) {
				short firstCellNum = -1; // 该行的第一个单元格
				short lastCellNum = -1; // 该行的最后一个单元格

				// 检查是否有有效信息
				boolean isEmptyRow = true;

				// 遍历该行的每一个单元格，找出第一个和最后一个有数据的单元格
				// 列正向搜索
				for (short cellNum = row.getFirstCellNum(); cellNum >= 0 && cellNum <= row.getLastCellNum(); cellNum++) {
					Cell cell = row.getCell(cellNum);
					if (cell != null && !isMerged(sheet, rowNum, cellNum)) {
						// 判断是否是空单元格
						String content = "" + getCellValue(cell);
						if (content.length() > 0) {
							if (firstCellNum == -1) {
								firstCellNum = cellNum;
								lastCellNum = cellNum;
							}
							isEmptyRow = false;
							break;
						}

					}
				}
				if (!isEmptyRow) {
					// 列反向搜索
					for (short cellNum = row.getLastCellNum(); cellNum > firstCellNum; cellNum--) {
						// 列尾范围
						Cell cell = row.getCell(cellNum);
						if (cell != null && !isMerged(sheet, rowNum, cellNum)) {
							// 判断是否是空单元格
							String content = "" + getCellValue(cell);
							if (content.length() > 0) {
								lastCellNum = (short) Math.max((int) lastCellNum, (int) cellNum);
								break;
							}
						}
					}
				}

				if (!isEmptyRow) {
					if (firstRowNum == -1) {
						firstRowNum = rowNum;
						lastRowNum = rowNum;
					} else {
						lastRowNum = (short) Math.max((int) lastRowNum, (int) rowNum);
					}
				}

				if (firstCellNum != -1) {
					firstColNum = firstColNum == -1 ? firstCellNum
							: (short) Math.min((int) firstColNum, (int) firstCellNum);
					lastColNum = (short) Math.max((int) lastColNum, (int) lastCellNum);
				}
			}
		}
		
		if(firstRowNum!=-1) {
			result = new HashMap<String, Integer>();
			result.put("firstRowNum", firstRowNum);
			result.put("firstColNum", firstColNum);
			result.put("lastRowNum", lastRowNum);
			result.put("lastColNum", lastColNum);
		}

		return result;
	}

	/**
	 * 解析表格范围(已知表格范围之外的区域)
	 * 
	 * @param list 已知表格列表
	 */
	private boolean parseTableRange(List<DataTable> list) {
		boolean result = false;
		
		// 实现思路，扫描起始行，避开已有表格已占满的行
		// 分析表格区域，没有数据的区域可能还有表格线，因此表格范围可能比数据区范围大, 表尾有的数据可能没有刷表线
		int minRowNum = firstRowNum == -1 ? sheet.getFirstRowNum() : firstRowNum;
		int maxRowNum = Math.max(lastRowNum, sheet.getLastRowNum());

		int minColNum = firstColNum;
		int maxColNum = lastColNum;

		for (int rowNum = minRowNum; rowNum <= maxRowNum; rowNum++) {
			if (sheet.getRow(rowNum) != null) {// 如果行不为空，
				Row row = sheet.getRow(rowNum);
				for (int colNum = minColNum; colNum <= maxColNum; colNum++) { // 循环该行的每一个单元格
					Cell cell = row.getCell(colNum);
					if (cell != null && !isMerged(sheet, rowNum, colNum)) {

						// 表格起始位置确定了吗？
						if (tableFirstRowNum == -1
								&& isLeftTop(sheet, firstRowNum, firstColNum, maxRowNum, lastColNum, rowNum, colNum)) {
							// 没确定，但是确定这个单元格是表格的左上角
							tableFirstRowNum = rowNum;
							tableFirstColNum = colNum;
							minColNum = tableFirstColNum;
						}
						// 如果起始位置确定了，但还没有确定右边界，那这个单元格是表格的右上角吗？
						if (tableFirstRowNum != -1 && tableLastColNum == -1
								&& isRightTop(sheet, firstRowNum, firstColNum, maxRowNum, lastColNum, rowNum, colNum)) {
							tableLastColNum = colNum + getMergerCellRegionCol(sheet, rowNum, colNum) - 1;
							maxColNum = tableLastColNum;
						}
						// 如果起始位置确定了，那这个单元格是表格结束位置吗？
						if (tableFirstRowNum != -1 && isRightBottom(sheet, firstRowNum, firstColNum, maxRowNum,
								lastColNum, rowNum, colNum)) {
							tableLastRowNum = rowNum + getMergerCellRegionRow(sheet, rowNum, colNum) - 1;
							tableLastColNum = colNum + getMergerCellRegionCol(sheet, rowNum, colNum) - 1;
						}
						if (tableLastRowNum != -1) {
							break;
						}
					}
				}
			}
			if (tableLastRowNum > 0) {
				break;
			}
		}

		// 按表格线是否找到表格范围
		if (tableFirstRowNum == -1) {
			// 没有找到，查找第一个列数最多的行为表格起始行
			for (int rowNum = minRowNum; rowNum <= maxRowNum; rowNum++) {
				if (sheet.getRow(rowNum) != null) {// 如果行不为空，
					Row row = sheet.getRow(rowNum);
					for (int colNum = firstColNum; colNum <= lastColNum; colNum++) { // 循环该行的每一个单元格
						Cell cell = row.getCell(colNum);
						if (cell != null && !isMerged(sheet, rowNum, colNum)) {

							// 表格起始位置确定了吗？
							if (tableFirstRowNum == -1) {
								// 没确定

								// 这个单元格是表格的左上角吗？右边到最大列号连续内容，下边到最大数据行每行都有内容
								if (isLeftTop(cell, row, sheet, firstRowNum, firstColNum, maxRowNum, lastColNum, rowNum,
										colNum)) {
									// 是，确定表格的起点
									tableFirstRowNum = rowNum;
									tableFirstColNum = colNum;
									tableLastRowNum = maxRowNum;
									tableLastColNum = lastColNum;
								}
							}
						}
					}
				}
			}
		}
		
		result = tableFirstRowNum != -1;
		
		return result;
	}

	/**
	 * 解析新的数据记录范围，(排除开已知表格范围)
	 * 
	 * @param list 已知表格列表
	 */
	private void parseRecordRange(List<DataTable> list) {
		// 编程思路，扫描有效数据区，绕开已知表格区域，检索标题行
		headFirstRowNum = tableFirstRowNum == -1 ? 0 : tableFirstRowNum;
		boolean success;
		int scanTableFirstColNum;
		int scanTableLastColNum;
		do {
			// 假定这个范围就是标题行
			success = true;
			headLastRowNum = headFirstRowNum;
			// 遍历标题行区域，获取标题行行数
			scanTableFirstColNum = tableFirstColNum == -1 ? 0 : tableFirstColNum;
			scanTableLastColNum = tableLastColNum == -1 ? lastColNum : tableLastColNum;
			fieldTree = new FieldTree(); // 清空字段列表
			for (int colNum = scanTableFirstColNum; colNum <= scanTableLastColNum; colNum++) {

				int colSpan = 1;
				int headRowNum = headLastRowNum;
				if (colCodeFirstRowNum != -1 && headRowNum >= colCodeFirstRowNum && headRowNum <= colCodeLastRowNum) {
					headRowNum = colCodeFirstRowNum - 1;
				}
				CellRangeAddress cra = getMergerCellRegion(sheet, headLastRowNum, colNum);
				if (cra != null) {
					colSpan = cra.getLastColumn() - cra.getFirstColumn() + 1; // 得到合并的列数
					headLastRowNum = Math.max(headLastRowNum, cra.getLastRow());
					headFirstRowNum = Math.min(headFirstRowNum, cra.getFirstRow());
					headRowNum = headLastRowNum;
				}

				if (colSpan > 3 || colSpan / (tableLastColNum - tableFirstColNum + 1) > 0.3) {
					// 单元格太宽，应非标题行，从下一行重新查找最后标题行
					headFirstRowNum = ++headLastRowNum;
					success = false;
					break;
				}

				// 下一行不论是栏目编号行还是数据行，跨列数都应一致，除非是二维表首
				int dataRowNum = headLastRowNum + 1;
				if (colCodeFirstRowNum != -1 && dataRowNum >= colCodeFirstRowNum && dataRowNum <= colCodeLastRowNum) {
					dataRowNum = colCodeLastRowNum - 1;
				}
				int nextColSpan = 1;
				CellRangeAddress nextCra = getMergerCellRegion(sheet, headLastRowNum + 1, colNum);
				if (nextCra != null) {
					nextColSpan = nextCra.getLastColumn() - nextCra.getFirstColumn() + 1; // 得到合并的列数
				}

				// 除了二维表的行首，其他数据列暂不考虑跨列的情况
				if ((colNum == scanTableFirstColNum && Math.abs(colSpan - nextColSpan) > 2)
						|| colSpan != nextColSpan && colNum > scanTableFirstColNum + 1) {
					// 单元格太宽，应非标题行，从下一行重新查找最后标题行
					headFirstRowNum = ++headLastRowNum;
					success = false;
					break;
				}

				// 除了第一栏字段外，添加失败，就不是正确的标题行，第一栏为空的应可能是二维表
				if (addFieldTree(headRowNum, colNum, scanTableLastColNum) == null
						&& (fieldTree.getSubTitleList().size() > 0 || colNum > 2)) {
					// 不能添加为有效的字段，就不是正确的标题行
					if (colNum < scanTableLastColNum * 0.66) {
						headFirstRowNum = ++headLastRowNum;
						success = false;
						break;
					}
				}

				if (colSpan > 1) {
					colNum += colSpan - 1;
				}
			}
		} while (!success && headFirstRowNum <= lastRowNum);

		// 从数据行往下至表格结束前检索表尾注解区域
		for (int j = headLastRowNum + 1; j <= tableLastRowNum; j++) {
			for (int i = firstRecordColNum; i >= 0 && i <= lastRecordColNum; i++) {
				int cs = getMergerCellRegionCol(sheet, j, i);
				if (cs > 1) {
					footerFirstRowNum = j;
					lastRecordRowNum = footerFirstRowNum - 1;
					footerLastRowNum = lastRowNum;
					break;
				}
			}
			if (footerFirstRowNum != -1) {
				break;
			}
		}
	}

	private FieldTree addFieldTree(int headRowNum, int colNum, int scanTableLastColNum) {
		FieldTree field = null;

		Cell cell1 = getCell(sheet, headRowNum, colNum);
		short color1f = cell1.getCellStyle().getFillForegroundColor();

		String titleName = getCellValue(cell1, CellType.STRING);
		if (titleName != null) {
			titleName = titleName.replaceAll("[\\u00A0|\\u3000|\\u0020]+", "");
		}

		int rowNum = headRowNum;
		if (titleName.length() == 0) {
			// 无有效标题，向上查找有效标题, 注意跳过可能有的栏目编号行
			for (rowNum = headRowNum - 1; rowNum >= headFirstRowNum; rowNum--) {
				if (colCodeFirstRowNum != -1 && rowNum <= colCodeFirstRowNum && rowNum <= colCodeLastRowNum) {
					rowNum = colCodeFirstRowNum;
					continue;
				}
				titleName = getCellValue(getCell(sheet, rowNum, colNum));
				if (titleName.length() > 0) {
					break;
				}
			}
		}

		if (colCodeFirstRowNum == -1) {
			// 还未确定过是否有栏目编号行
			if (parseNumberColCode(titleName) >= 0) {
				// 像是一个栏目编号, 如果本行剩余栏目，除了为空的，均像栏目编号的话，那就确定该行尾栏目编号行
				boolean result = true;
				Set<Integer> numSet = new HashSet<Integer>();
				int startNum = parseNumberColCode(titleName);
				numSet.add(startNum);
				for (int i = colNum + 1; i <= scanTableLastColNum; i++) {
					String next = getCellValue(getCell(sheet, rowNum, i));
					if (next != null && next.length() > 0) {
						Integer nextNum = parseNumberColCode(next);
						if (nextNum == -1 || numSet.contains(nextNum)) {
							result = false;
							break;
						}
					}
				}
				if (result) {
					colCodeFirstRowNum = rowNum;
					colCodeLastRowNum = colCodeFirstRowNum;
					headLastRowNum = Math.max(headLastRowNum, colCodeLastRowNum);
				}
			} else {
				// 不像是栏目编号，看看下一行是否像一个栏目编号
				Cell cell = getCell(sheet, headLastRowNum + 1, colNum);
				if (cell != null) {
					short color2f = cell.getCellStyle().getFillForegroundColor();
					if (color1f == color2f) {
						String name = getCellValue(cell, CellType.STRING);
						if (name != null) {
							name = name.replaceAll("[\\u00A0]+", " ");
						}
						if (scanTableLastColNum - colNum + 1 >= 10 && parseNumberColCode(name) >= 0) {
							// 栏目数较多，如10栏及以上，本栏像是一个栏目编号, 如果本行剩余栏目，除了为空的，均像栏目编号的话，那就确定该行尾栏目编号行
							boolean result = true;
							Set<Integer> numSet = new HashSet<Integer>();
							int startNum = parseNumberColCode(name);
							numSet.add(startNum);
							for (int i = colNum + 1; i <= scanTableLastColNum; i++) {
								String next = getCellValue(getCell(sheet, headLastRowNum + 1, i), CellType.STRING);
								if (next != null) {
									Integer nextNum = parseNumberColCode(next);
									if (nextNum == -1 || numSet.contains(nextNum)) {
										result = false;
										break;
									}
								}
							}
							if (result) {
								colCodeFirstRowNum = headLastRowNum + 1;
								colCodeLastRowNum = colCodeFirstRowNum;
								headLastRowNum = Math.max(headLastRowNum, colCodeLastRowNum);
							}
						}
					}
				}
			}
		}

		if (titleName != null && titleName.trim().length() > 0) {
			field = addFieldTree(titleName, colNum);
		} else {
			// 空白单元格
			if (fieldTree.getSubTitleList().size() > 0 && colNum != scanTableLastColNum
					|| fieldTree.getSubTitleList().size() == 0 && (scanTableLastColNum - colNum) <= 2) {
				// 左边已经有标题了，这又不是最末列，或者虽然还没有有效标题，但该行也没剩两行了，可能不是标题行

				// 如果在现有标题行范围内向上也找不到有效字段列，或者也不是复合标题行的的首列，就不是标题行，
				for (int i = headLastRowNum - 1; i >= headFirstRowNum; i--) {
					titleName = getCellValue(getCell(sheet, headLastRowNum, colNum));
					if (titleName != null && titleName.trim().length() > 0) {
						break;
					}
				}
				if (titleName != null && titleName.trim().length() > 0) {
					field = addFieldTree(titleName, colNum);
				}
			}
		}

		return field;
	}

	private FieldTree addFieldTree(String titleName, int colNum) {
		FieldTree field = null;

		if (titleName.length() > 0) {
			field = new FieldTree();
			field.setName(titleName);
			field.setColNum(colNum);
			fieldTree.addField(field, titleName);
			if (firstRecordColNum == -1) {
				firstRecordColNum = colNum;
				lastRecordColNum = colNum;
			} else {
				lastRecordColNum = colNum;
			}
		}

		return field;
	}

	/**
	 * 栏目名称是否是一个数字栏目编号 如 1,2,3,(1),(2),（3），a,b,c,A,B,C,(A),(B),(C) 等
	 * 
	 * @param name
	 * @return
	 */
	private int parseNumberColCode(String name) {
		int number = -1;

		String colCode = name.replaceAll("（", "(").replaceAll("）", ")").trim();
		if (colCode.startsWith("(") && colCode.endsWith(")")) {
			colCode = colCode.substring(1, colCode.length() - 1).trim().toLowerCase();
		}
		// 剥离可能的描述
		if (colCode.indexOf("(") > 0 && colCode.indexOf(")") >= 0 && colCode.indexOf(")") > colCode.indexOf("(")) {
			colCode = colCode.substring(0, colCode.indexOf("(")).trim();
		}
		colCode = colCode.trim().toLowerCase();
		if (name.length() == 1 && name.charAt(0) >= 'a' && name.charAt(0) <= 'z') {
			number = name.charAt(0) - 'a';
		} else {
			try {
				number = Integer.parseInt(colCode);
				if (number < 0) {
					number = -1;
				}
			} catch (NumberFormatException e) {
			}
		}

		return number;
	}

	/**
	 * 解析表格标题 有效区域前1/3范围行内第一个合并了4分之三的行, 或者整行唯一有文本的单元格
	 * 
	 * @return
	 */
	private boolean parseTitle() {
		boolean hasTitle = false;
		// 从表格前三行以内开始查找,最多找5行
		int startRowNum = Math.max(firstRowNum, tableFirstRowNum - 3);
		for (int i = startRowNum; i < startRowNum + 5; i++) {
			int cs = getMergerCellRegionCol(sheet, i, firstColNum);
			if ((lastColNum - firstColNum + 1) - cs < (lastColNum - firstColNum + 1) / 4) {
				titleRowNum = i;
				titleColNum = firstColNum;
				// titleLocation = getColName(firstColNum)+(i+1);
				title = getCellValue(sheet.getRow(i).getCell(firstColNum));
				hasTitle = true;
				break;
			}
		}
		// 如果没有找到，表格前三行以内开始查找,最多找5行，左边三分之二区域内是否有整行唯一有文本的单元格
		if (!hasTitle) {
			for (int i = startRowNum; i < startRowNum + 5; i++) {

				for (int j = firstColNum; j <= (tableLastColNum - tableFirstColNum + 1) / 3 * 2
						+ tableFirstColNum; j++) {
					String value = null;
					Cell cell = getCell(sheet, i, j);
					if(cell!=null) {
						value = getCellValue(cell);
					}
					if (value != null && value.length() > 0) {
						// 检查剩余列是否为空
						title = value;
						for (int k = j + 1; k <= lastColNum; k++) {
							value = getCellValue(sheet.getRow(i).getCell(k));
							if (value != null && value.length() > 0) {
								title = null;
								break;
							}
						}
						break;
					}
				}
				if (title != null) {
					hasTitle = true;
					break;
				}
			}
		}
		return hasTitle;
	}

	/**
	 * 获得指定列的列序号
	 * 
	 * @param colNum
	 * @return
	 */
	public int getColName(String colName) {
		int num = 0;
		int result = 0;
		int length = colName.length();
		for (int i = 0; i < length; i++) {
			char ch = colName.charAt(length - i - 1);
			num = (int) (ch - 'A' + 1);
			num *= Math.pow(26, i);
			result += num;
		}
		return result;
	}

	/**
	 * 获得指定列的列名
	 * 
	 * @param colNum
	 * @return
	 */
	public String getColName(int colNum) {
		String colName = "";

		int count = 1;
		while ((int) (colNum / Math.pow(26, count - 1)) > 0) {
			int w3 = ((int) (colNum % Math.pow(26, count))) / ((int) Math.pow(26, count - 1));
			char c = (char) (w3 + 65);
			colName = c + colName;
			count++;
		}

		return colName;
	}

	/**
	 * 解析指定工作表中的数据表
	 * @param sheet
	 * @param index
	 * @return
	 */
	public static List<DataTable> praseDataTables(Sheet sheet, int index) {
		List<DataTable> list = new ArrayList<DataTable>();

		// 解析数据区范围
		Map<String, Integer> range = parseDataRange(sheet);
		if(range!=null) {
			int firstRowNum = range.get("firstRowNum");
			int firstColNum = range.get("firstColNum");
			int lastRowNum = range.get("lastRowNum");
			int lastColNum = range.get("lastColNum");
			
			DataTable dataTable = new DataTable(sheet, index, firstRowNum, firstColNum, lastRowNum, lastColNum, list);
			
			while (dataTable != null) {
				list.add(dataTable);
				dataTable = dataTable.praseNextDataTable(sheet, index, firstRowNum, firstColNum, lastRowNum, lastColNum, list);
			}
			// 滤除没有解析成功的区域
			List<DataTable> removeList = new ArrayList<DataTable>();
			for(DataTable dTable:list) {
				if(dTable.getFieldTree().getFieldMap().size()==0) {
					removeList.add(dTable);
				}
			}
			list.removeAll(removeList);
		}

		return list;
	}

	/**
	 * 解析指定工作表中的数据表中指定区域内的下一张数据表(除开已解析表格)
	 * @param sheet
	 * @param index
	 * @param firstRowNum
	 * @param firstColNum
	 * @param lastRowNum
	 * @param lastColNum
	 * @param list
	 * @return
	 */
	private DataTable praseNextDataTable(Sheet sheet, int index, int firstRowNum, int firstColNum, int lastRowNum, int lastColNum, List<DataTable> list) {
		DataTable dataTable = new DataTable(sheet, index, firstRowNum, firstColNum, lastRowNum, lastColNum, list);

		if(dataTable.getFirstRowNum()==-1) {
			dataTable = null;
		}

		return dataTable;
	}

	/**
	 * 是表格上边界单元格吗
	 * 
	 * @param sheet
	 * @param tableFirstRow
	 * @param tableFirstCol
	 * @param tableLastRow
	 * @param tableLastCol
	 * @param rowNum
	 * @param colNum
	 * @return
	 */
	private static boolean isBorderTop(Sheet sheet, int tableFirstRow, int tableFirstCol, int tableLastRow,
			int tableLastCol, int rowNum, int colNum) {
		boolean result = false;

		if (tableFirstRow != -1 && rowNum == tableFirstRow && colNum >= tableFirstCol
				&& (tableLastCol == -1 || colNum <= tableLastCol)) {
			result = true;
		}

		return result;
	}

	/**
	 * 是表格左边界单元格吗
	 * 
	 * @param sheet
	 * @param tableFirstRow
	 * @param tableFirstCol
	 * @param tableLastRow
	 * @param tableLastCol
	 * @param rowNum
	 * @param colNum
	 * @return
	 */
	private static boolean isBorderLeft(Sheet sheet, int tableFirstRow, int tableFirstCol, int tableLastRow,
			int tableLastCol, int rowNum, int colNum) {
		boolean result = false;

		if (tableFirstRow != -1 && colNum == tableFirstCol && rowNum >= tableFirstRow
				&& (tableLastRow == -1 || rowNum <= tableLastRow)) {
			result = true;
		}

		return result;
	}

	/**
	 * 是在表格中的单元格吗
	 * 
	 * @param sheet
	 * @param tableFirstRow
	 * @param tableFirstCol
	 * @param tableLastRow
	 * @param tableLastCol
	 * @param rowNum
	 * @param colNum
	 * @return
	 */
	private static boolean inTable(Sheet sheet, int tableFirstRow, int tableFirstCol, int tableLastRow,
			int tableLastCol, int rowNum, int colNum) {
		boolean result = false;

		if (tableFirstRow != -1 && rowNum >= tableFirstRow && (tableLastRow == -1 || rowNum <= tableLastRow)
				&& colNum >= tableFirstCol && (tableLastCol == -1 || colNum <= tableLastCol)) {
			result = true;
		}

		return result;
	}

	/**
	 * 取得单元格的值
	 * 
	 * @param cell
	 * @return
	 * @throws IOException
	 */
	public static String getCellValue(Cell cell) {
		return getCellValue(cell, (CellType) null);
	}

	/**
	 * 取得单元格的值
	 * 
	 * @param cell
	 * @return
	 * @throws IOException
	 */
	public static String getCellStringValue(Cell cell) {
		return getCellValue(cell, CellType.STRING);
	}

	public static Double getCellDoubleValue(Cell cell) {
		Double value = null;

		String cellValue;
		if (cell != null) {
			switch (cell.getCellType()) {
			case NUMERIC:
				value = cell.getNumericCellValue();
				break;
			case STRING:
				cellValue = cell.getStringCellValue();
				if (cellValue != null && cellValue.length() > 0) {
					value = Double.parseDouble(cellValue);
				}
				break;
			case FORMULA:
				Object wb = cell.getRow().getSheet().getWorkbook();

				FormulaEvaluator formulaEvaluator;
				if (wb instanceof HSSFWorkbook) {
					formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
				} else if (wb instanceof XSSFWorkbook) {
					formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
				} else {
					formulaEvaluator = null;
				}
				try {
					cellValue = String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
					if (cellValue != null && cellValue.length() > 0) {
						value = Double.parseDouble(cellValue);
					}
				} catch (Exception e) {
				}
				break;
			default:
				break;
			}
		}
		return value;
	}

	public static Integer getCellIntValue(Cell cell) {
		Integer value = null;

		String cellValue;
		if (cell != null) {
			switch (cell.getCellType()) {
			case NUMERIC:
				Double doubleValue = cell.getNumericCellValue();
				if (doubleValue != null) {
					value = doubleValue.intValue();
				}
				break;
			case STRING:
				cellValue = cell.getStringCellValue();
				if (cellValue != null && cellValue.length() > 0) {
					value = Integer.parseInt(cellValue);
				}
				break;
			case FORMULA:
				Object wb = cell.getRow().getSheet().getWorkbook();

				FormulaEvaluator formulaEvaluator;
				if (wb instanceof HSSFWorkbook) {
					formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
				} else if (wb instanceof XSSFWorkbook) {
					formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
				} else {
					formulaEvaluator = null;
				}
				try {
					cellValue = String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
					if (cellValue != null && cellValue.length() > 0) {
						value = new Double(cellValue).intValue();
					}
				} catch (Exception e) {
				}
				break;
			default:
				break;
			}
		}
		return value;
	}

	/**
	 * 取得单元格的值
	 * 
	 * @param cell
	 * @return
	 * @throws IOException
	 */
	public static Date getCellDateValue(Cell cell) {
		Date result = null;

		if (cell != null) {
			switch (cell.getCellType()) {
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					short dataFormat = cell.getCellStyle().getDataFormat();

					switch (dataFormat) {
					case 20:
						result = cell.getDateCellValue();
						break;
					case 57:
						result = cell.getDateCellValue();
						break;
					case 31:
					case 176:
						result = cell.getDateCellValue();
						break;
					case 184:
					default:
						result = cell.getDateCellValue();
						break;
					}
				} else {
					boolean isDate = false;
					double d = cell.getNumericCellValue();
					if (DateUtil.isValidExcelDate(d)) {
						CellStyle style = cell.getCellStyle();
						if (style == null) {
							isDate = false;
						} else {
							int i = style.getDataFormat();
							String f;
							switch (i) {
							case 14:
								f = "yyyy-MM-dd";
								isDate = true;
								break;
							case 20:
								f = "HH:mm";
								isDate = true;
								break;
							case 31:
								f = "yyyy年m月d日";
								isDate = true;
								break;
							case 32:
								f = "h时mm分";
								break;
							case 57:
								f = "yyyy年m月";
								isDate = true;
								break;
							case 58:
								f = "m月d日";
								isDate = true;
								break;
							default:
								f = style.getDataFormatString().replaceAll("[\"|\']", "")
										.replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");
								if (f != null) {
									isDate = HSSFDateUtil.isADateFormat(i, f);
								} else {

								}
								break;
							}
						}
					}
					if (isDate) {
						result = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
					}
				}
				break;
			case STRING:
				String cellValue = cell.getStringCellValue();
				if (cellValue != null && cellValue.length() > 0) {
					try {
						result = com.lzyyj.common.DateUtil.convertStringToDate(null, cellValue);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				break;
			case FORMULA:
				Object wb = cell.getRow().getSheet().getWorkbook();

				FormulaEvaluator formulaEvaluator;
				if (wb instanceof HSSFWorkbook) {
					formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
				} else if (wb instanceof XSSFWorkbook) {
					formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
				} else {
					formulaEvaluator = null;
				}
				try {
					String cellValue1 = formulaEvaluator.evaluate(cell).getStringValue();
					result = com.lzyyj.common.DateUtil.convertStringToDate(null, cellValue1);
				} catch (Exception e) {
					result = null;
				}
				break;
			default:
				break;
			}
		}

		return result;
	}

	/**
	 * 取得单元格的值
	 * 
	 * @param cell
	 * @return
	 * @throws IOException
	 */
	public static String getCellValue(Cell cell, CellType cellTypeInt) {
		String result = "";

		if (cell != null) {
			if (cellTypeInt != null && cell.getCellType() != cellTypeInt && cellTypeInt != CellType.STRING) {
//				throw new Exception("数据类型不是预期的格式！");
			} else {
				switch (cell.getCellType()) {
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						short dataFormat = cell.getCellStyle().getDataFormat();
						SimpleDateFormat sdf;
						Date date;
						switch (dataFormat) {
						case 20:
							sdf = new SimpleDateFormat("HH:mm");
							date = cell.getDateCellValue();
							result = sdf.format(date);
							break;
						case 57:
							sdf = new SimpleDateFormat("yyyy年MM月");
							date = cell.getDateCellValue();
							result = sdf.format(date);
							break;
						case 31:
						case 176:
							sdf = new SimpleDateFormat("yyyy年MM月dd日");
							date = cell.getDateCellValue();
							result = sdf.format(date);
							break;
						default:
							double value = cell.getNumericCellValue();
							CellStyle style = cell.getCellStyle();
							DecimalFormat format = new DecimalFormat();
							String temp = style.getDataFormatString();
							// 单元格设置成常规
							if ("General".equals(temp)) {
								format.applyPattern("#");
							}
							result = format.format(value);
							break;
						}
					} else {
						boolean isDate = false;
						double d = cell.getNumericCellValue();
						if (DateUtil.isValidExcelDate(d)) {
							CellStyle style = cell.getCellStyle();
							if (style == null) {
								isDate = false;
							} else {
								int i = style.getDataFormat();
								String f;
								switch (i) {
								case 14:
									f = "yyyy-MM-dd";
									isDate = true;
									break;
								case 20:
									f = "HH:mm";
									isDate = true;
									break;
								case 31:
									f = "yyyy年m月d日";
									isDate = true;
									break;
								case 32:
									f = "h时mm分";
									break;
								case 57:
									f = "yyyy年m月";
									isDate = true;
									break;
								case 58:
									f = "m月d日";
									isDate = true;
									break;
								default:
									f = style.getDataFormatString().replaceAll("[\"|\']", "")
											.replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");
									if (f != null) {
										isDate = HSSFDateUtil.isADateFormat(i, f);
									} else {

									}
									break;
								}
							}
						}
						if (isDate) {
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
							return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString().trim();
						} else {
							if (cellTypeInt != null && cellTypeInt == CellType.STRING) {
								DecimalFormat df = new DecimalFormat("0");
								return df.format(cell.getNumericCellValue());
							} else {
								return "" + cell.getNumericCellValue();
							}
						}
					}
					break;
				case STRING:
					result = cell.getStringCellValue();
					break;
				case FORMULA:
					Object wb = cell.getRow().getSheet().getWorkbook();

					FormulaEvaluator formulaEvaluator;
					if (wb instanceof HSSFWorkbook) {
						formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
					} else if (wb instanceof XSSFWorkbook) {
						formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
					} else {
						formulaEvaluator = null;
					}
					try {
						result = String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
					} catch (Exception e) {
						result = null;
					}
					break;
				case BLANK:
					result = cell.getRichStringCellValue().toString();
					break;
				case BOOLEAN:
					result = "" + cell.getBooleanCellValue();
					break;
				case ERROR:
					result = "" + cell.getErrorCellValue();
					break;
				default:
					result = "";
					break;
				}
			}
		}

		return result;
	}

	/**
	 * 判断单元格是否是被合并的单元格，及第一单元格之外的其他单元格
	 * 
	 * @param sheet   工作表
	 * @param cellRow 被判断的单元格的行号
	 * @param cellCol 被判断的单元格的列号
	 * @return
	 * @throws IOException
	 */
	private static boolean isMerged(Sheet sheet, int cellRow, int cellCol) {
		boolean retVal = false;
		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
			int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
			int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
			int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
			int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
			if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
				if (cellCol >= firstCol && cellCol <= lastCol) {
					retVal = !(cellRow == firstRow && cellCol == firstCol); // 不是 主单元格吗？
					break;
				}
			}
		}
		return retVal;
	}

	/**
	 * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
	 * 
	 * @param sheet   工作表
	 * @param cellRow 被判断的单元格的行号
	 * @param cellCol 被判断的单元格的列号
	 * @return
	 * @throws IOException
	 */
	private static int getMergerCellRegionCol(Sheet sheet, int cellRow, int cellCol) {
		int retVal = 1;
		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
			int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
			int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
			int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
			int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
			if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
				if (cellCol >= firstCol && cellCol <= lastCol) {
					retVal = lastCol - firstCol + 1; // 得到合并的列数
					break;
				}
			}
		}
		return retVal;
	}

	/**
	 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
	 * 
	 * @param sheet   表单
	 * @param cellRow 被判断的单元格的行号
	 * @param cellCol 被判断的单元格的列号
	 * @return
	 */
	private static int getMergerCellRegionRow(Sheet sheet, int cellRow, int cellCol) {
		int retVal = 1;

		CellRangeAddress cra1 = getMergerCellRegion(sheet, cellRow, cellCol);
		if (cra1 != null) {
			retVal = cra1.getLastRow() - cra1.getFirstRow() + 1; // 得到合并的行数
		}

		return retVal;
	}

	/**
	 * 获取合并的单格范围
	 * 
	 * @param sheet   表单
	 * @param cellRow 被判断的单元格的行号
	 * @param cellCol 被判断的单元格的列号
	 * @return
	 */
	private static CellRangeAddress getMergerCellRegion(Sheet sheet, int cellRow, int cellCol) {
		CellRangeAddress result = null;

		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
			// 判断该单元格是否是在合并单元格中
			if (cellRow >= cra.getFirstRow() && cellRow <= cra.getLastRow() && cellCol >= cra.getFirstColumn()
					&& cellCol <= cra.getLastColumn()) {
				result = cra;
				break;
			}
		}

		return result;
	}

	/**
	 * 单元格背景色转换
	 * 
	 * @param hc
	 * @return
	 */
	private String convertToStardColor(Color hc) {
		StringBuffer sb = new StringBuffer("");
		return sb.toString();
	}

	/**
	 * 单元格水平对齐方式
	 * 
	 * @param alignment
	 * @return
	 */
	private String convertAlignToHtml(HorizontalAlignment alignment) {
		String align = "left";
		switch (alignment) {
		case CENTER:
			align = "center";
			break;
		case RIGHT:
			align = "right";
			break;
		case LEFT:
		default:
			align = "left";
			break;
		}
		return align;
	}

	/**
	 * 单元格垂直对齐方式
	 * 
	 * @param verticalAlignment
	 * @return
	 */
	private String convertVerticalAlignToHtml(VerticalAlignment verticalAlignment) {
		String valign = "middle";
		switch (verticalAlignment) {
		case BOTTOM:
			valign = "bottom";
			break;
		case CENTER:
			valign = "center";
			break;
		case TOP:
			valign = "top";
			break;
		default:
			break;
		}
		return valign;
	}

	/**
	 * 是否是表格的左上角
	 * 
	 * @return
	 */
	private boolean isLeftTop(Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol, int cellRow,
			int cellCol) {
		boolean result = false;

		// 左边是否没有表线，第一列，或者左边单元格无上边线
		boolean leftTop = (cellCol == firstCol || notHasBorderTop(getCell(sheet, cellRow, cellCol - 1)));

		/// 上边单元格没有左边线吗，第一行，或上单元格无左边线
		boolean topLeft = (cellCol == firstCol || notHasBorderLeft(getCell(sheet, cellRow - 1, cellCol)));

		// 右边是否有表线，当前单元格有上边线
		boolean top = !notHasBorderTop(getCell(sheet, cellRow, cellCol));

		/// 下边单元格没有左边线吗，当前单元格有左边线
		boolean left = !notHasBorderLeft(getCell(sheet, cellRow, cellCol));

		result = leftTop && topLeft && top && (left || cellCol == firstCol); // 第一列忽略容易丢失的左边线

		return result;
	}

	/**
	 * 是否是表格的右上角，以单元格的右上角为起点，判断上下左右四个方向是否有编写进行判断
	 * 
	 * @return
	 */
	private boolean isRightTop(Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol, int cellRow,
			int cellCol) {
		boolean result = false;

		int colSpan = getMergerCellRegionCol(sheet, cellRow, cellCol);
		if (colSpan > 1) {
			cellCol = cellCol + colSpan - 1;
		}
		int rowSpan = getMergerCellRegionRow(sheet, cellRow, cellCol);
		if (rowSpan > 1) {
			cellRow = cellRow + rowSpan - 1;
		}

		// 右边没有表线：最后一列，或者右边单元格无上边线
		boolean right = (cellCol == lastCol || notHasBorderTop(getCell(sheet, cellRow, cellCol + 1)));

		// 上边没有表线：第一行，或上单元格无右边线
		boolean top = (cellRow == firstRow || notHasBorderRight(getCell(sheet, cellRow - 1, cellCol)));

		// 左边有表线：当前单元格有上边线
		boolean left = !notHasBorderTop(getCell(sheet, cellRow, cellCol));

		// 下边有表线：当前单元格有右边线
		boolean bottom = !notHasBorderRight(getCell(sheet, cellRow, cellCol));

		result = right && top && left && bottom;

		return result;
	}

	/**
	 * 是否是表格的左上角，右边连续有内容，下边到最大数据行每行有内容的列没有超出本行有内容列的范围
	 * 
	 * @return
	 */
	private boolean isLeftTop(Cell cell, Row row, Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol,
			int cellRow, int cellCol) {
		boolean result = false;

		String content = getCellValue(cell);
		if (content != null && content.length() > 0) {
			// 下一列到末尾都有内容
			boolean isFull = true;
			int lastC = -1;
			for (int cellNum = cellCol + 1; cellNum <= lastCol; cellNum++) {
				Cell nextCell = row.getCell(cellNum);
				if (nextCell != null) {
					String str = getCellValue(nextCell);
					if (lastC == -1) {
						// 第一个没有数据的，前一列是最后一个标题列
						if (str == null || str.length() == 0) {
							lastC = cellNum - 1;
						}
					} else {
						// 已经确定了最后一个标题列了
						if (str != null && str.length() > 0) {
							// 已经确定了最后一个标题列了，又出现了
							isFull = false;
							break;
						}
					}
				} else {
					isFull = false;
				}
				if (!isFull) {
					break;
				}
			}
			if (isFull) {
				if (lastC == -1) {
					lastC = lastCol;
				}
				// 下边到最大数据行每行有内容的列没有超出本行有内容列的范围
				for (int rowNum = cellRow + 1; rowNum <= lastRow; rowNum++) {
					Row nextRow = sheet.getRow(rowNum);
					if (nextRow != null) {
						boolean right = true;
						for (int colNum = firstCol; colNum < lastCol; colNum++) {
							Cell nextCell = nextRow.getCell(cellCol);
							if (nextCell != null) {
								String str = getCellValue(nextCell);
								if (str != null && str.length() > 0 && (colNum < cellCol || colNum > lastC)) {
									right = false;
									break;
								}
							}
						}
						if (!right) {
							isFull = false;
						}
					}
					if (!isFull) {
						break;
					}
				}
				result = isFull;
			}
		}

		return result;
	}

	/**
	 * 是否是表格的右下角
	 * 
	 * @return
	 * @throws IOException
	 */
	private boolean isRightBottom(Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol, int cellRow,
			int cellCol) {
		boolean result = false;

		int colSpan = getMergerCellRegionCol(sheet, cellRow, cellCol);
		if (colSpan > 1) {
			cellCol = cellCol + colSpan - 1;
		}
		int rowSpan = getMergerCellRegionRow(sheet, cellRow, cellCol);
		if (rowSpan > 1) {
			cellRow = cellRow + rowSpan - 1;
		}

		// 右边没有表线，最后一列，或者右边单元格无下边线
		boolean right = (cellCol == lastCol || notHasBorderBottom(getCell(sheet, cellRow, cellCol + 1)));

		/// 下边没有表线，最后一行，或下单元格无右边线
		boolean bottom = (cellCol == firstCol || notHasBorderRight(getCell(sheet, cellRow + 1, cellCol)));

		// 左边有表线，当前单元格有下边线
		boolean left = !notHasBorderBottom(getCell(sheet, cellRow, cellCol));

		/// 上边有表线，当前单元格有右边线
		boolean top = !notHasBorderRight(getCell(sheet, cellRow, cellCol));

		result = right && bottom && left && top;

		return result;
	}

	/**
	 * 没有上边线吗
	 * 
	 * @return
	 */
	private boolean notHasBorderTop(Cell cell) {
		boolean result = true;

		if (cell != null) {
			CellStyle cellStyle = cell.getCellStyle();
			BorderStyle borderStyle = cellStyle.getBorderTop();
			result = (BorderStyle.NONE == borderStyle);
		}

		return result;
	}

	/**
	 * 没有右边线吗
	 * 
	 * @return
	 */
	private boolean notHasBorderRight(Cell cell) {
		boolean result = true;

		if (cell != null) {
			CellStyle cellStyle = cell.getCellStyle();
			BorderStyle borderStyle = cellStyle.getBorderRight();
			result = (BorderStyle.NONE == borderStyle);
		}

		return result;
	}

	/**
	 * 没有下边线吗
	 * 
	 * @return
	 */
	private boolean notHasBorderBottom(Cell cell) {
		boolean result = true;

		if (cell != null) {
			CellStyle cellStyle = cell.getCellStyle();
			BorderStyle borderStyle = cellStyle.getBorderBottom();
			result = (BorderStyle.NONE == borderStyle);
		}

		return result;
	}

	/**
	 * 没有左边线吗
	 * 
	 * @return
	 */
	private boolean notHasBorderLeft(Cell cell) {
		boolean result = true;

		if (cell != null) {
			CellStyle cellStyle = cell.getCellStyle();
			BorderStyle borderStyle = cellStyle.getBorderLeft();
			result = (BorderStyle.NONE == borderStyle);
		}

		return result;
	}

	private Cell getCell(Sheet sheet, int cellRow, int cellCol) {
		Cell cell = null;

		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
			int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
			int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
			int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
			int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
			if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
				if (cellCol >= firstCol && cellCol <= lastCol) {
					cellRow = firstRow;
					cellCol = firstCol;
					break;
				}
			}
		}

		Row row = sheet.getRow(cellRow);
		if (row != null) {
			cell = row.getCell(cellCol);
		}

		return cell;
	}

	public boolean isValidate() {
		boolean isValidate = false;

		if (sheet != null && firstRowNum >= 0 && lastRowNum >= 0 && lastRowNum - firstRowNum > 0) {
			isValidate = true;
		}

		return isValidate;
	}

	public String getSheetName() {
		return sheet.getSheetName();
	}

	public int getSheetIndex() {
		return index;
	}

	public String toTableHtml() {
		Workbook workbook = sheet.getWorkbook();

		StringBuffer tableSB = new StringBuffer();

		// 构造Table
		tableSB.append(
				"<table style=\"border:1px solid #000;border-width:0px 0 0 0;margin:2px 0 2px 0;border-collapse:collapse;\">");
		for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
			if (sheet.getRow(rowNum) != null) {// 如果行不为空，
				Row row = sheet.getRow(rowNum);
				int height = (int) (row.getHeight() / 15.625); // 行的高度
				tableSB.append("<tr height=\"" + height
						+ "\" style=\"border:1px 1px 1px 1px solid #000;border-width:1px 1px 1px 1px;margin:2px 0 2px 0;\">");
				for (int cellNum = firstColNum; cellNum <= lastColNum; cellNum++) { // 循环该行的每一个单元格
					Cell cell = row.getCell(cellNum);
					if (cell != null && !isMerged(sheet, rowNum, cellNum)) {
						String content = "" + getCellValue(cell);
						if (content.startsWith("时间：")) {
							log.debug("性别");
						}

						// 判断是否是表格起始位置
						if (tableFirstColNum == -1 && isLeftTop(sheet, firstRowNum, firstColNum, lastRowNum, lastColNum,
								rowNum, cellNum)) {
							tableFirstRowNum = rowNum;
							tableFirstColNum = cellNum;
						}
						// 判断表格结束位置
						if (tableFirstRowNum != -1 && isRightBottom(sheet, firstRowNum, firstColNum, lastRowNum,
								lastColNum, rowNum, cellNum)) {
							tableLastRowNum = rowNum;
							tableLastColNum = cellNum;
						}

						StringBuffer tdSB = new StringBuffer("<td style=\"margin:2px 0 2px 0;");

						if (isBorderTop(sheet, tableFirstRowNum, tableFirstColNum, tableLastRowNum, tableLastColNum,
								rowNum, cellNum)) {
							tdSB.append(" border-top: 1px;");
						}

						if (isBorderLeft(sheet, tableFirstRowNum, tableFirstColNum, tableLastRowNum, tableLastColNum,
								rowNum, cellNum)) {
							tdSB.append(" border-left: 1px;");
						}
						if (inTable(sheet, tableFirstRowNum, tableFirstColNum, tableLastRowNum, tableLastColNum, rowNum,
								cellNum)) {
							tdSB.append(" border:1px solid #000; border-right-width:1px;border-bottom-width:1px;");
						}

						CellStyle cellStyle = cell.getCellStyle();
						short boldWeight = -1; // 字体粗细
						short fontHeight = -1;
						if (workbook instanceof HSSFWorkbook) {
							HSSFWorkbook hssfWorkBook = (HSSFWorkbook) workbook;

							HSSFPalette palette = hssfWorkBook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
							Color hColor = palette.getColor(cellStyle.getFillForegroundColor());
							Color hColor2 = palette.getColor(cellStyle.getFillBackgroundColor());

							String bgColor = convertToStardColor(hColor);// 背景颜色
							String fontColor = convertToStardColor(hColor2); // 字体颜色

							if (((HSSFCellStyle) cellStyle).getFont(hssfWorkBook).getBold()) {
								// boldWeight =
								// ((HSSFCellStyle)cellStyle).getFont(hssfWorkBook).getBoldweight(); // 字体粗细
								boldWeight = 900;
							}

							fontHeight = ((HSSFCellStyle) cellStyle).getFont(hssfWorkBook).getFontHeight();
							if (bgColor != null && !"".equals(bgColor.trim())) {
								tdSB.append(" background-color:" + bgColor + "; ");
							}
							if (fontColor != null && !"".equals(fontColor.trim())) {
								tdSB.append(" color:" + fontColor + "; ");
							}
						}
						if (boldWeight != -1) {
							tdSB.append(" font-weight:").append(boldWeight).append(";");
						}
						if (fontHeight <= 100) {
							tdSB.append(" font-size: " + fontHeight + "%;");
						} else {
						}
						tableSB.append(tdSB + "\"");

						int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); //
						int cellReginCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列
						int cellReginRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行
						String align = convertAlignToHtml(cellStyle.getAlignment()); //
						String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

						tableSB.append(" align=\"" + align + "\" valign=\"" + vAlign + "\" ");
						if (cellReginCol > 1) {
							tableSB.append(" colspan=\"" + cellReginCol + "\"");
						} else {
							tableSB.append(" width=\"" + width + "\"");
						}
						if (cellReginRow > 1) {
							tableSB.append(" rowspan=\"" + cellReginRow + "\"");
						}

						tableSB.append(">" + content + "</td>");

					}
				}
				tableSB.append("</tr>");
			}
		}
		tableSB.append("</table>");
		return tableSB.toString();
	}

	public List<List<Object>> getRecords() {
		List<List<Object>> list = new ArrayList<List<Object>>();

		if (headLastRowNum >= 0) {
			// 确定数据行结尾
			for (int j = headLastRowNum + 1; j <= tableLastRowNum; j++) {
				List<Object> record = new ArrayList<Object>();
				int rowSpan = 1;
				for (int i = firstRecordColNum; i >= 0 && i <= lastRecordColNum; i++) {
					// 检查处理复合行记录，通常是注解栏为多行
					int rs = getMergerCellRegionRow(sheet, j, i);
					rowSpan = Math.max(rowSpan, rs);
				}
				for (int i = firstRecordColNum; i >= 0 && i <= lastRecordColNum; i++) {
					int k = 0;
					Cell cell = getCell(sheet, j, i);
					String value = getCellValue(cell);
					int rs = getMergerCellRegionRow(sheet, j, i);
					k += rs;
					while (k < rowSpan) {
						Cell cell1 = getCell(sheet, j + k, i);
						String value1 = getCellValue(cell1);
						int rs1 = getMergerCellRegionRow(sheet, j, i);
						k += rs1;
						value += "\r\n" + value1;
					}
					record.add(value);
				}
				list.add(record);
				if (rowSpan > 1) {
					j += rowSpan - 1;
				}
			}
		}

		return list;
	}

}
